﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Windows.Forms;
using NmbpProject2.DboClasses;
using Oracle.DataAccess.Client;

namespace NmbpProject2.DataLayer {

  internal class CompApplicationDL {

    public CompApplication findByPersonComp (Person person, Competition competition) {
      switch (DbConfig.DBTYPE) {
        case dbtype.Db40:
          IList<CompApplication> applications = DataConfig.db.Query (
            delegate (CompApplication match) { return match.Person == person && match.Competition == competition; }
          );
          if (applications.Count == 0) {
            return null;
          }
          else {
            CompApplication application = applications[0];
            return application;
          }
          break;
        case dbtype.GeoSpatial:
          break;
        case dbtype.Oracle:
          break;
        case dbtype.Xml:
          break;
      }
      return null;
    }

    public List<CompApplication> findByPerson (Person person) {
      switch (DbConfig.DBTYPE) {
        case dbtype.Db40:
          IList<CompApplication> result = DataConfig.db.Query (
            delegate (CompApplication match) { return match.Person == person; }
          );
          if (result.Count == 0) {
            return null;
          }
          else {
            List<CompApplication> applications = new List<CompApplication> ();
            foreach (CompApplication application in result) {
              applications.Add (application);
            }
            return applications;
          }
          break;
        case dbtype.GeoSpatial:
          break;
        case dbtype.Oracle:
          break;
        case dbtype.Xml:
          break;
      }
      return null;
    }

    public List<CompApplication> findByLanguage (ProgLanguage language, bool onlyLanguage) {
      switch (DbConfig.DBTYPE) {
        case dbtype.Db40:
          IList<CompApplication> result = DataConfig.db.Query (delegate (CompApplication match) {
            if (onlyLanguage) {
              if (match.ProgLanguages.Count == 1) {
                return match.ProgLanguages[0] == language;
              }
              else {
                return false;
              }
            }
            else {
              foreach (
                  ProgLanguage lang in match.ProgLanguages
                  ) {
                if (lang == language) {
                  return true;
                }
              }
              return false;
            }
          });
          if (result.Count == 0) {
            return null;
          }
          else {
            List<CompApplication> applications = new List<CompApplication> ();
            foreach (CompApplication application in result) {
              applications.Add (application);
            }
            return applications;
          }
          break;
        case dbtype.GeoSpatial:
          break;
        case dbtype.Oracle:
          break;
        case dbtype.Xml:
          break;
      }
      return null;
    }

    public List<CompApplication> findByCategory (Category category, bool onlyCategory) {
      switch (DbConfig.DBTYPE) {
        case dbtype.Db40:
          IList<CompApplication> result = DataConfig.db.Query (delegate (CompApplication match) {
            if (onlyCategory) {
              if (match.Categories.Count == 1) {
                return match.Categories[0] == category;
              }
              else {
                return false;
              }
            }
            else {
              foreach (Category cat in match.Categories) {
                if (cat == category) {
                  return true;
                }
              }
              return false;
            }
          });
          if (result.Count == 0) {
            return null;
          }
          else {
            List<CompApplication> applications = new List<CompApplication> ();
            foreach (CompApplication application in result) {
              applications.Add (application);
            }
            return applications;
          }
          break;
        case dbtype.GeoSpatial:
          break;
        case dbtype.Oracle:
          break;
        case dbtype.Xml:
          break;
      }
      return null;
    }

    public List<CompApplication> getAllApplications () {
      var applications = new List<CompApplication> ();
      switch (DbConfig.DBTYPE) {
        case dbtype.Db40:
          var result = DataConfig.db.QueryByExample (typeof (CompApplication));
          foreach (CompApplication application in result) {
            applications.Add (application);
          }
          return applications;
          break;
        case dbtype.GeoSpatial:
          break;
        case dbtype.Oracle:
          var oracle = new OracleConnectionClass ();
          oracle.openConnection ();
          var cmd = new OracleCommand (
            "SELECT DEREF(person).firstname, " +
            "DEREF(person).lastname, " +
            "DEREF(person).jmbg, " +
            "DEREF(competition).name, " +
            "DEREF(competition).dateHeld, " +
            "dateApplied " +
            "FROM applications",
            oracle.conn
            );
          cmd.CommandType = CommandType.Text;
          var dt = new DataTable ();
          new OracleDataAdapter (cmd).Fill (dt);
          foreach (DataRow row in dt.Rows) {
            string firstName = row[0].ToString ();
            string lastName = row[1].ToString ();
            long jmbg = long.Parse (row[2].ToString ());
            string name = row[3].ToString ();
            DateTime compDate = (DateTime) (row[4]);
            DateTime appDate = (DateTime) (row[5]);
            var progList = ProgLanguageDL.getAllLanguages (
              "applicationslanguages", string.Format (
              "deref(deref(application).person).jmbg like '{0}' and deref(deref(application).competition).name like '{1}'", jmbg.ToString (), name));
            var catList = CategoryDL.getAllCategories (
              "applicationscategories", string.Format (
              "deref(deref(application).person).jmbg like '{0}' and deref(deref(application).competition).name like '{1}'", jmbg.ToString (), name));
            CompApplication app = new CompApplication (
              new Person (firstName, lastName, jmbg),
              progList,
              appDate,
              catList
              );
            app.Competition = new Competition (name, compDate, null, null);
            applications.Add (app);

          }
          oracle.closeConnection ();
          break;
        case dbtype.Xml:
          break;
      }
      return applications;
    }

    public void insertApplication (
        Person person,
        List<ProgLanguage> progLanguages,
        DateTime date,
        List<Category> categories,
        Competition competition) {
      switch (DbConfig.DBTYPE) {
        case dbtype.Db40:
          CompApplicationDL cDL = new CompApplicationDL ();
          // Update competition and store application
          Competition tempComp = (Competition) DataConfig.db.QueryByExample (competition)[0];
          CompApplication application = new CompApplication (person, progLanguages, date, categories);
          tempComp.addApplication (application);
          DataConfig.db.Store (application);
          DataConfig.db.Store (tempComp);
          break;
        case dbtype.GeoSpatial:
          break;
        case dbtype.Oracle:
          try {
            var oracle = new OracleConnectionClass ();
            oracle.openConnection ();
            var cmd = new OracleCommand ();
            cmd.Connection = oracle.conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = String.Format (
                "insert into applications " +
                "select ref(person), ref(comp), to_date('{0}','dd.mm.yyyy.')) from person, competitions comp " +
                "where (person.jmbg = '{1}') and (comp.name = '{2}') and (comp.dateheld = to_date('{3}','dd.mm.yyyy.')",
                date.ToString ("dd.MM.yyyy."), person.Jmbg.ToString (), competition.Name, competition.Date.ToString ("dd.MM.yyyy."));
            cmd.ExecuteNonQuery ();
            foreach (var language in progLanguages) {
              cmd.CommandText = string.Format ("insert into applicationslanguages " +
              "select ref(app), ref (lang) from applications app, languages lang " +
              "where (app.person.jmbg='{0}') and (app.competition.name = '{1}') and (lang.name='{2}')",
              person.Jmbg.ToString (), competition.Name, language.Name);
              cmd.ExecuteNonQuery ();
            }
            foreach (var category in categories) {
              cmd.CommandText = string.Format ("insert into applicationscategories " +
              "select ref(app), ref (cat) from applications app, categories cat  " +
              "where (app.person.jmbg='{0}') and (app.competition.name = '{1}') and (cat.name='{2}')",
              person.Jmbg.ToString (), competition.Name, category.Name);
              cmd.ExecuteNonQuery ();
            }
            oracle.closeConnection ();
          }
          catch (Exception e) {
            MessageBox.Show (e.Message, "ERROR");
          }
          break;
        case dbtype.Xml:
          break;
      }
    }

    public void delete (CompApplication application) {
      switch (DbConfig.DBTYPE) {
        case dbtype.Db40:
          DataConfig.db.Delete (application);
          break;
        case dbtype.GeoSpatial:
          break;
        case dbtype.Oracle:
          try {
            var oracle = new OracleConnectionClass ();
            oracle.openConnection ();
            var cmd = new OracleCommand ();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = oracle.conn;
            string where =
                string.Format (
                    " where deref(deref(application).person).jmbg = '{0}' and deref(deref(application).competition).name = '{1}'",
                    application.Person.Jmbg, application.Competition.Name);
            cmd.CommandText = string.Format ("delete from applicationscategories{0}", where);
            cmd.ExecuteNonQuery ();
            cmd.CommandText = string.Format ("delete from applicationslanguages{0}", where);
            cmd.ExecuteNonQuery ();
            cmd.CommandText = string.Format ("delete from applications where deref(person).jmbg='{0}' and deref(competition).name = '{1}'",
                                            application.Person.Jmbg.ToString (), application.Competition.Name);
            cmd.ExecuteNonQuery ();
            oracle.closeConnection ();
          }
          catch (Exception e) {
            e.Message.ToString ();
          }
          break;
        case dbtype.Xml:
          break;
      }
    }

    public void update (
        CompApplication application,
        Person person,
        List<ProgLanguage> progLanguages,
        DateTime date,
        List<Category> categories,
        Competition competition) {
      switch (DbConfig.DBTYPE) {
        case dbtype.Db40:
          CompApplication updatedApplication = (CompApplication) DataConfig.db.QueryByExample (application)[0];
          Competition oldCompetition = updatedApplication.Competition;
          updatedApplication.Categories = categories;
          updatedApplication.Date = date;
          updatedApplication.Person = person;
          updatedApplication.ProgLanguages = progLanguages;
          // update competitions
          oldCompetition.removeApplication (application);
          DataConfig.db.Store (oldCompetition);
          competition.addApplication (application);
          DataConfig.db.Store (updatedApplication);
          DataConfig.db.Store (competition);
          break;
        case dbtype.GeoSpatial:
          break;
        case dbtype.Oracle:
          try {
            var oracle = new OracleConnectionClass ();
            oracle.openConnection ();
            var cmd = new OracleCommand ();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = oracle.conn;
            string where =
                string.Format (
                   " where deref(deref(application).person).jmbg = '{0}' and deref(deref(application).competition).name = '{1}'",
                    person.Jmbg, competition.Name);
            //deletes
            cmd.CommandText = string.Format ("delete from applicationscategories{0}", where);
            cmd.ExecuteNonQuery ();
            cmd.CommandText = string.Format ("delete from applicationslanguages{0}", where);
            cmd.ExecuteNonQuery ();
            //inserts
            foreach (var language in progLanguages) {
              cmd.CommandText = string.Format ("insert into applicationslanguages " +
              "select ref(app), ref (lang) from applications app, languages lang " +
              "where (app.person.jmbg='{0}') and (app.competition.name = '{1}') and (lang.name='{2}')",
              person.Jmbg.ToString (), competition.Name, language.Name);
              cmd.ExecuteNonQuery ();
            }
            foreach (var category in categories) {
              cmd.CommandText = string.Format ("insert into applicationscategories " +
              "select ref(app), ref (cat) from applications app, categories cat  " +
              "where (app.person.jmbg='{0}') and (app.competition.name = '{1}') and (cat.name='{2}')",
              person.Jmbg.ToString (), competition.Name, category.Name);
              cmd.ExecuteNonQuery ();
            }
            //update
            cmd.CommandText =
                string.Format (
                    "update competitions " +
                    "set person = (select ref(person) from person where person.jmbg = '{0}'), " +
                    "set competition = (select ref(comp) from competitions comp where name = '{1}'), " +
                    "set dateapplied = to_date('{2}','dd.mm.yyyy.') " +
                    "where (deref(person).jmbg ='{3}' and deref(competition).name = '{4}')",
                    person.Jmbg, competition.Name, date.ToString ("dd.MM.yyyy."), application.Person.Jmbg, application.Date.ToString ("dd.MM.yyyy."));
            cmd.ExecuteNonQuery ();
            oracle.closeConnection ();
          }
          catch (Exception e) {
            MessageBox.Show (e.Message);
          }
          break;
        case dbtype.Xml:
          break;
      }
    }

    public void update (CompApplication application) {
      DataConfig.db.Store (application);
    }
  }
}